# /usr/bin/python3.5

import pymysql
from pprint import pprint

config2 = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': '123456',
    'db': 'i2p'}

config1 = {
    'host': '180.97.80.42',
    'port': 10086,
    'user': 'root',
    'passwd': 'iraintest',
    'db': 'i2p'}

config_select = {
    'databases': 'SHOW DATABASES',
    'tables': 'SHOW TABLES FROM {database_name}',
    'create_table': 'SHOW CREATE TABLE {table_name}',
    'columns': 'SHOW COLUMNS FROM {table_name}',
}

system_databases = ('mysql', 'information_schema', 'performance_schema')

result_databases = []
result_tables = {}
result_column = {}

connect1 = pymysql.connect(**config2)
cursor1 = connect1.cursor()

connect2 = pymysql.connect(**config1)
cursor2 = connect2.cursor()

# 查询数据库所有的数据表
cursor1.execute(config_select['databases'])
cursor2.execute(config_select['databases'])

row_databases1 = cursor1.fetchall()
cursor1.close()
databases1 = [x[0] for x in row_databases1 if x[0] not in system_databases]
row_databases2 = cursor2.fetchall()
cursor2.close()
databases2 = [x[0] for x in row_databases2 if x[0] not in system_databases]

result_databases = [x for x in databases2 if x not in databases1]

table_construct1 = {}

for x in result_databases:
    cursor2.execute(config_select['tables'].replace('{database_name}', x))
    row_tables = cursor2.fetchall()
    result_tables[x] = [y[0] for y in row_tables]

    for k, v in result_tables.items():
        for value in v:
            cursor1 = connect1.cursor()
            cursor1.execute(config_select['columns'].replace('{table_name}', k + '.' + value))
            row_table = cursor1.fetchall()
            table_construct1[k][value] = row_table
            cursor1.close()

print(table_construct1)

connect1.close()
connect2.close()
